<?php

namespace App\Http\Controllers\Charge;

use App\Http\Controllers\Controller;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Spreadsheet;

class ChargeStatiController extends Controller
{
    /**
     * 充电统计
     *
     * @param  Request  $request
     *
     * @return mixed
     */
    public function chargeStatiList(Request $request)
    {
        $params = $countParams = $request->all();
        unset($countParams['pageNumber'], $countParams['pageSize']);
        $data = $this->getChargeList($params)
            ->groupBy('line')
            ->groupBy('license_plate_num')
            ->orderBy('line')
            ->get()
            ->toArray();
        $dataCount = $this->getChargeList($countParams)
            ->groupBy('line')
            ->groupBy('license_plate_num')
            ->get()
            ->count();
        if (isset($params['start_date_time'])) {
            $start_date_time = date('Y-m-d', strtotime($params['start_date_time']));
        } else {
            $start_date_time = date('Y-m-d');
        }
        if (isset($params['end_date_time'])) {
            $end_date_time = date('Y-m-d', strtotime($params['end_date_time']));
        } else {
            $end_date_time = date('Y-m-d');
        }
        if ($data) {
            $data = $this->chargetransData($data, $start_date_time, $end_date_time);
        }
        return response()->formatJson(['result' => $data, 'count' => $dataCount]);
    }

    public function getChargeList($params)
    {
        $query = DB::table('akbus_charge')
            ->select(
                'license_plate_num',
                'line',
                DB::raw('SUM(trading_power) as total_trading_power'),
                DB::raw('SUM(act_trading_amt) as total_trading_amt')
            );

        if (isset($params['line']) && '-1' !== $params['line']) {
            $query->where('line', $params['line']);
        }
        if (isset($params['department']) && '-1' !== $params['department']) {
            $query->where('department', 'like', '%' . $params['department'] . '%');
        }
        if (isset($params['start_date_time'])) {
            $start_date_time = date('Y-m-d 00:00:00', strtotime($params['start_date_time']));
            $query->where('charging_end_at', '>=', $start_date_time);
        }
        if (isset($params['end_date_time'])) {
            $end_date_time = date('Y-m-d 23:59:59', strtotime($params['end_date_time']));
            $query->where('charging_end_at', '<=', $end_date_time);
        }
        if (isset($params['pageNumber'], $params['pageSize'])) {
            $query
                ->limit($params['pageSize'])
                ->offset(($params['pageNumber'] - 1) * $params['pageSize']);
        }

        return $query;
    }

    public function chargetransData($data, $start_date_time, $end_date_time)
    {
        foreach ($data as $key => $v) {
            $data[$key]['date'] = $start_date_time.'-'.$end_date_time;
            $data[$key]['line'] = (int) $v['line'] === 0 ? '停运' : $v['line'].'路';
        }
        return $data;
    }

    /**
     * 收入统计
     *
     * @param  Request  $request
     *
     * @return mixed
     */
    public function incomeStatiList(Request $request)
    {
        $params = $countParams = $request->all();
        unset($countParams['pageNumber'], $countParams['pageSize']);
        $data = $this->getIncomeList($params)->groupBy('线路')->groupBy('车牌号')->orderBy('线路')->get()->toArray();
        $dataCount = $this->getIncomeList($countParams)->groupBy('线路')->groupBy('车牌号')->get()->toArray();
        if (isset($params['start_date_time'])) {
            $start_date_time = date('Y-m-d', strtotime($params['start_date_time']));
        } else {
            $start_date_time = date('Y-m-d');
        }
        if (isset($params['end_date_time'])) {
            $end_date_time = date('Y-m-d', strtotime($params['end_date_time']));
        } else {
            $end_date_time = date('Y-m-d');
        }
        if ($data) {
            $data = $this->incomeTransData($data, $start_date_time, $end_date_time);
        }
        return response()->formatJson(['result' => $data, 'count' => count($dataCount)]);
    }

    public function getIncomeList($params)
    {
        $query = DB::table('v_income')
            ->select(
                '车牌号',
                '线路',
                DB::raw('SUM(包车收入) as total_chartered_bus'),
                DB::raw('SUM(一卡通收入) as total_IPass'),
                DB::raw('SUM(邮储收入) as total_post'),
                DB::raw('SUM(现金收入) as total_cash')
            );

        if (isset($params['line']) && '-1' !== $params['line']) {
            $query->where('线路', $params['line']);
        }
        if (isset($params['start_date_time'])) {
            $start_date_time = date('Y-m-d 00:00:00', strtotime($params['start_date_time']));
            $query->where('日期', '>=', $start_date_time);
        } else {
            $start_date_time = date('Y-m-d');
            $query->where('日期', '>=', $start_date_time);
        }
        if (isset($params['end_date_time'])) {
            $end_date_time = date('Y-m-d 23:59:59', strtotime($params['end_date_time']));
            $query->where('日期', '<=', $end_date_time);
        } else {
            $end_date_time = date('Y-m-d');
            $query->where('日期', '>=', $end_date_time);
        }
        if (isset($params['pageNumber']) && isset($params['pageSize'])) {
            $query
                ->limit($params['pageSize'])
                ->offset(($params['pageNumber'] - 1) * $params['pageSize']);
        }

        return $query;
    }

    public function incomeTransData($data, $start_date_time, $end_date_time)
    {
        foreach ($data as $key => $v) {
            $data[$key]['date'] = $start_date_time.'-'.$end_date_time;
            // 线路为0的显示为停运
            $data[$key]['线路'] = $v['线路'] === 0 ? '停运' : $v['线路'];
            $data[$key]['total_money'] =
                round((
                    (float) $v['total_chartered_bus'] +
                    (float) $v['total_IPass'] +
                    (float) $v['total_post'] +
                    (float) $v['total_cash']
                ), 2);
        }
        return $data;
    }

    /**
     * 里程统计
     *
     * @param  Request  $request
     *
     * @return mixed
     */
    public function kmStatiList(Request $request)
    {
        $params = $countParams = $request->all();
        unset($countParams['pageNumber'], $countParams['pageSize']);
        $data = $this->getKmList($params)
            ->groupBy('license_plate_num')
            ->groupBy('line')
            ->orderBy('line')
            ->orderBy('license_plate_num')
            ->get()
            ->toArray();
        $dataCount = $this->getKmList($countParams)->groupBy('license_plate_num')->get()->toArray();
        if (isset($params['start_date_time'])) {
            $start_date_time = date('Y-m-d', strtotime($params['start_date_time']));
        } else {
            $start_date_time = date('Y-m-d');
        }
        if (isset($params['end_date_time'])) {
            $end_date_time = date('Y-m-d', strtotime($params['end_date_time']));
        } else {
            $end_date_time = date('Y-m-d');
        }
        if ($data) {
            $data = $this->kmTransData($data, $start_date_time, $end_date_time);
        }
        return response()->formatJson(['result' => $data, 'count' => count($dataCount)]);
    }

    public function getKmList($params)
    {
        $query = DB::table('akbus_mileage as c')
            ->leftJoin('akbus_bus_distribution as d', function ($join) {
                $join->on('d.license_plate_num', '=', 'c.license_plate_num')
                    ->on(DB::raw('to_days(c.date)'), '>=', DB::raw('to_days(d.date)'))
                    ->on(DB::raw('to_days(c.date)'), '<=', DB::raw('to_days(d.end_date)'));
            })
            ->select(
                'c.license_plate_num',
                'd.line',
                DB::raw('SUM(c.gps_mileage) as total_km')
            );

        if (isset($params['line']) && '-1' !== $params['line']) {
            $query->where('d.line', $params['line']);
        }

        if (isset($params['start_date_time'])) {
            $start_date_time = date('Y-m-d', strtotime($params['start_date_time']));
            $query->where('c.date', '>=', $start_date_time);
        } else {
            $start_date_time = date('Y-m-d');
            $query->where('c.date', '>=', $start_date_time);
        }
        if (isset($params['end_date_time'])) {
            $end_date_time = date('Y-m-d', strtotime($params['end_date_time']));
            $query->where('c.date', '<=', $end_date_time);
        } else {
            $end_date_time = date('Y-m-d');
            $query->where('c.date', '>=', $end_date_time);
        }
        if (isset($params['pageNumber']) && isset($params['pageSize'])) {
            $query
                ->limit($params['pageSize'])
                ->offset(($params['pageNumber'] - 1) * $params['pageSize']);
        }

        return $query;
    }

    public function kmTransData($data, $start_date_time, $end_date_time)
    {
        foreach ($data as $key => $v) {
            $data[$key]['date'] = $start_date_time.'-'.$end_date_time;
        }
        return $data;
    }

    /**
     * 逸华统计
     *
     * @param  Request  $request
     *
     * @return mixed
     */
    public function yhStatiList(Request $request)
    {
        $params = $countParams = $request->all();
        unset($countParams['pageNumber'], $countParams['pageSize']);
        $data = $this->getYhList($params)->groupBy('线路')->groupBy('车牌号')->orderBy('线路')->get()->toArray();
        $dataCount = $this->getYhList($countParams)->groupBy('线路')->groupBy('车牌号')->get()->count();
        if (isset($params['start_date_time'])) {
            $start_date_time = date('Y-m-d', strtotime($params['start_date_time']));
        } else {
            $start_date_time = date('Y-m-d');
        }
        if (isset($params['end_date_time'])) {
            $end_date_time = date('Y-m-d', strtotime($params['end_date_time']));
        } else {
            $end_date_time = date('Y-m-d');
        }
        if ($data) {
            $data = $this->yhTransData($data, $start_date_time, $end_date_time);
        }
        return response()->formatJson(['result' => $data, 'count' => $dataCount]);
    }

    public function getYhList($params): \Illuminate\Database\Query\Builder
    {
        $query = DB::table('v_gas_yh')
            ->select(
                '车牌号',
                '线路',
                DB::raw('SUM(金额) as total_amount'),
                DB::raw('SUM(气量) as total_volume')
            );

        if (isset($params['line']) && '-1' !== $params['line']) {
            $query->where('线路', $params['line']);
        }
        if (isset($params['start_date_time'])) {
            $start_date_time = date('Y-m-d 00:00:00', strtotime($params['start_date_time']));
            $query->where('日期', '>=', $start_date_time);
        } else {
            $start_date_time = date('Y-m-d');
            $query->where('日期', '>=', $start_date_time);
        }
        if (isset($params['end_date_time'])) {
            $end_date_time = date('Y-m-d 23:59:59', strtotime($params['end_date_time']));
            $query->where('日期', '<=', $end_date_time);
        } else {
            $end_date_time = date('Y-m-d');
            $query->where('日期', '>=', $end_date_time);
        }
        if (isset($params['pageNumber']) && isset($params['pageSize'])) {
            $query
                ->limit($params['pageSize'])
                ->offset(($params['pageNumber'] - 1) * $params['pageSize']);
        }

        return $query;
    }

    public function yhTransData($data, $start_date_time, $end_date_time)
    {
        foreach ($data as $key => $v) {
            $data[$key]['date'] = $start_date_time.'-'.$end_date_time;
        }
        return $data;
    }

    /**
     * 顺康统计
     *
     * @param  Request  $request
     *
     * @return mixed
     */
    public function skStatiList(Request $request)
    {
        $params = $countParams = $request->all();
        unset($countParams['pageNumber'], $countParams['pageSize']);
        $data = $this->getSkList($params)->groupBy('线路')->groupBy('车牌号')->orderBy('线路')->get()->toArray();
        $dataCount = $this->getSkList($countParams)->groupBy('线路')->groupBy('车牌号')->get()->count();

        if (isset($params['start_date_time'])) {
            $start_date_time = date('Y-m-d', strtotime($params['start_date_time']));
        } else {
            $start_date_time = date('Y-m-d');
        }
        if (isset($params['end_date_time'])) {
            $end_date_time = date('Y-m-d', strtotime($params['end_date_time']));
        } else {
            $end_date_time = date('Y-m-d');
        }
        if ($data) {
            $data = $this->skTransData($data, $start_date_time, $end_date_time);
        }
        return response()->formatJson(['result' => $data, 'count' => $dataCount]);
    }

    public function getSkList($params)
    {
        $query = DB::table('v_gas_sk')
            ->select(
                '车牌号',
                '线路',
                DB::raw('SUM(金额) as total_amount'),
                DB::raw('SUM(气量) as total_volume')
            );

        if (isset($params['line']) && '-1' !== $params['line']) {
            $query->where('线路', $params['line']);
        }

        $start_date_time = date('Y-m-d H:i:s', strtotime($params['start_date_time']));
        $end_date_time = date('Y-m-d H:i:s', strtotime($params['end_date_time']));
        $query->whereBetween('日期', [$start_date_time, $end_date_time]);

        if (isset($params['pageNumber'], $params['pageSize'])) {
            $query
                ->limit($params['pageSize'])
                ->offset(($params['pageNumber'] - 1) * $params['pageSize']);
        }

        return $query;
    }

    public function skTransData($data, $start_date_time, $end_date_time)
    {
        foreach ($data as $key => $v) {
            $data[$key]['date'] = $start_date_time.'-'.$end_date_time;
            $data[$key]['线路'] = (int) $v['线路'] !== 0 ? $v['线路'] : '停运';
        }
        return $data;
    }

    /**
     * 逸华检气罐统计
     *
     * @param  Request  $request
     *
     * @return mixed
     */
    public function yhAmtStatiList(Request $request)
    {
        $params = $countParams = $request->all();
        unset($countParams['pageNumber'], $countParams['pageSize']);
        $data = $this->getYhAmtList($params)
            ->groupBy('line')
            ->groupBy('license_plate_num')
            ->orderBy('line')
            ->get()
            ->toArray();
        $dataCount = $this->getYhAmtList($countParams)
            ->groupBy('line')
            ->groupBy('license_plate_num')
            ->orderBy('line')
            ->get()
            ->toArray();

        if ($data) {
            $data = $this->yhAmtTransData($data, $params['start_date_time'], $params['end_date_time']);
        }
        return response()->formatJson(['result' => $data, 'count' => count($dataCount)]);
    }

    public function getYhAmtList($params)
    {
        $query = DB::table('akbus_gas_yh_check as c')
            ->join('akbus_bus_distribution as d', function ($join) {
                $join->on('d.license_plate_num', '=', 'c.license_plate_num')
                    ->on('c.date', '>=', 'd.date')
                    ->on('c.date', '<=', 'd.end_date');
            })
            ->select(
                'c.license_plate_num',
                'd.line',
                DB::raw('SUM(c.amount) as total_amount'),
                DB::raw('SUM(c.volume) as total_volume')
            );

        if (isset($params['line']) && '-1' !== $params['line']) {
            $query->where('d.line', $params['line']);
        }

        if (isset($params['start_date_time'])) {
            $start_date_time = date('Y-m-d 00:00:00', strtotime($params['start_date_time']));
            $query->where('c.date', '>=', $start_date_time);
        } else {
            $start_date_time = date('Y-m-d');
            $query->where('c.date', '>=', $start_date_time);
        }

        if (isset($params['end_date_time'])) {
            $end_date_time = date('Y-m-d 23:59:59', strtotime($params['end_date_time']));
            $query->where('c.date', '<=', $end_date_time);
        } else {
            $end_date_time = date('Y-m-d');
            $query->where('c.date', '>=', $end_date_time);
        }
        if (isset($params['pageNumber'], $params['pageSize'])) {
            $query
                ->limit($params['pageSize'])
                ->offset(($params['pageNumber'] - 1) * $params['pageSize']);
        }

        return $query;
    }

    public function yhAmtTransData($data, $start_date_time, $end_date_time)
    {
        foreach ($data as $key => $v) {
            $data[$key]['date'] = $start_date_time.'-'.$end_date_time;
        }
        return $data;
    }

    /**
     * 导出充电登记
     *
     * @param  Request  $request
     *
     * @throws \PhpOffice\PhpSpreadsheet\Exception
     * @throws \PhpOffice\PhpSpreadsheet\Writer\Exception
     */
    public function exportChargeStati(Request $request)
    {
        $params = $request->input();
        $Letter = [
            'A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U',
        ];
        $data = $this->getChargeList($params)
            ->groupBy('line')
            ->groupBy('license_plate_num')
            ->orderBy('line')
            ->get()
            ->toArray();
        $line = $this->getChargeList($params)
            ->groupBy('line')
            ->orderBy('line')
            ->get()
            ->toArray();
        if (isset($params['start_date_time'])) {
            $start_date_time = date('Y-m-d', strtotime($params['start_date_time']));
        } else {
            $start_date_time = date('Y-m-d');
        }
        if (isset($params['end_date_time'])) {
            $end_date_time = date('Y-m-d', strtotime($params['end_date_time']));
        } else {
            $end_date_time = date('Y-m-d');
        }
        // 创建一个Spreadsheet对象
        $spreadsheet = new Spreadsheet();
        // 设置文档属性
        $spreadsheet->getProperties()->setCreator('Maarten Balliauw')
            ->setLastModifiedBy('Maarten Balliauw')
            ->setTitle('Office 2007 XLSX Test Document')
            ->setSubject('Office 2007 XLSX Test Document')
            ->setDescription('Test document for Office 2007 XLSX, generated using PHP classes.')
            ->setKeywords('office 2007 openxml php')
            ->setCategory('Test result file');
        // 添加表头
        $spreadsheet->setActiveSheetIndex(0)
            ->setCellValue('A1', '日期')
            ->setCellValue('B1', '线路')
            ->setCellValue('C1', '车牌号')->setCellValue('D1', '交易金额')->setCellValue('E1', '交易电量');

        //居中
        $numberStyleCenter = [
            'alignment' => [
                'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER,
                'vertical'   => \PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER,
            ],
        ];

        $spreadsheet->getActiveSheet()->getColumnDimension('A')->setWidth(30);
        $spreadsheet->getActiveSheet()->getColumnDimension('B')->setWidth(12);
        $spreadsheet->getActiveSheet()->getColumnDimension('C')->setWidth(8);
        $spreadsheet->getActiveSheet()->getColumnDimension('D')->setWidth(12);
        $spreadsheet->getActiveSheet()->getColumnDimension('E')->setWidth(12);
        for ($i = 0; $i < 5; $i++) {
            $spreadsheet->getActiveSheet()->getStyle($Letter[$i].'1')->applyFromArray($numberStyleCenter)
                ->getAlignment()->setWrapText(true);
        }
        $num = 2;
        $line_i = 0;
        $total_trading_amt = 0;
        $total_trading_power = 0;
        foreach ($line as $k => $v) {
            foreach ($data as $dk => $dv) {
                if ($v['line'] === $dv['line']) {
                    $spreadsheet->getActiveSheet()
                        ->setCellValue('A'.($num + $k + $line_i), $start_date_time.'-'.$end_date_time);
                    if ((int) $dv['line'] === 0) {
                        $showLine = '停运';
                    } else if ((int) $dv['line'] === 999) {
                        $showLine = '其它';
                    } else {
                        $showLine = $dv['line'].'路';
                    }
                    $spreadsheet->getActiveSheet()->setCellValue('B'.($num + $k + $line_i), $showLine);
                    $spreadsheet->getActiveSheet()->setCellValue('C'.($num + $k + $line_i), $dv['license_plate_num']);
                    $spreadsheet->getActiveSheet()->setCellValue('D'.($num + $k + $line_i), $dv['total_trading_amt']);
                    $spreadsheet->getActiveSheet()->setCellValue('E'.($num + $k + $line_i), $dv['total_trading_power']);
                    for ($i = 0; $i < 5; $i++) {
                        $spreadsheet->getActiveSheet()->getStyle($Letter[$i].($num + $k + $line_i))
                            ->getFont()->setSize(10);
                        $spreadsheet->getActiveSheet()->getStyle($Letter[$i].($num + $k + $line_i))
                            ->applyFromArray($numberStyleCenter)->getAlignment()->setWrapText(true);
                    }
                    $num++;
                }
            }
            $line_i++;
            for ($i = 0; $i < 5; $i++) {
                $spreadsheet->getActiveSheet()->getStyle($Letter[$i].($num + $k + $line_i - 1))->getFont()->setSize(10);
                $spreadsheet->getActiveSheet()->getStyle($Letter[$i].($num + $k + $line_i - 1))
                    ->applyFromArray($numberStyleCenter)->getAlignment()->setWrapText(true);
            }
            $spreadsheet->getActiveSheet()
                ->setCellValue('A'.($num + $k + $line_i - 1), $start_date_time.'-'.$end_date_time);
            $spreadsheet->getActiveSheet()->setCellValue('B'.($num + $k + $line_i - 1), '线路合计');
            $spreadsheet->getActiveSheet()->setCellValue('C'.($num + $k + $line_i - 1), '');
            $spreadsheet->getActiveSheet()->setCellValue('D'.($num + $k + $line_i - 1), $v['total_trading_amt']);
            $spreadsheet->getActiveSheet()->setCellValue('E'.($num + $k + $line_i - 1), $v['total_trading_power']);
            $total_trading_amt += $v['total_trading_amt'];
            $total_trading_power += $v['total_trading_power'];
        }
        for ($i = 0; $i < 5; $i++) {
            $spreadsheet->getActiveSheet()->getStyle($Letter[$i].($num + count($line) + $line_i))
                ->getFont()->setSize(10);
            $spreadsheet->getActiveSheet()->getStyle($Letter[$i].($num + count($line) + $line_i))
                ->applyFromArray($numberStyleCenter)->getAlignment()->setWrapText(true);
        }
        $spreadsheet->getActiveSheet()
            ->setCellValue('A'.($num + count($line) + $line_i), $start_date_time.'-'.$end_date_time);
        $spreadsheet->getActiveSheet()->setCellValue('B'.($num + count($line) + $line_i), '总合计');
        $spreadsheet->getActiveSheet()->setCellValue('C'.($num + count($line) + $line_i), '');
        $spreadsheet->getActiveSheet()->setCellValue('D'.($num + count($line) + $line_i), $total_trading_amt);
        $spreadsheet->getActiveSheet()->setCellValue('E'.($num + count($line) + $line_i), $total_trading_power);

        $spreadsheet->setActiveSheetIndex(0);
        // 重命名 worksheet
        $spreadsheet->getActiveSheet()->setTitle($start_date_time.'-'.$end_date_time);
        // 将活动工作表索引设置为第一个工作表，以便Excel将其作为第一个工作表打开
        // 将输出重定向到客户端的Web浏览器 (Xlsx)
        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        header('Content-Disposition: attachment;filename="'.$start_date_time.'-'.$end_date_time.'充电.xlsx"');
        header('Cache-Control: max-age=0');
        // 如果正在使用IE 9
        header('Cache-Control: max-age=1');
        // If you're serving to IE over SSL, then the following may be needed
        header('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); // Date in the past
        header('Last-Modified: '.gmdate('D, d M Y H:i:s').' GMT'); // always modified
        header('Cache-Control: cache, must-revalidate'); // HTTP/1.1
        header('Pragma: public'); // HTTP/1.0
        $writer = IOFactory::createWriter($spreadsheet, 'Xlsx');
        $writer->save('php://output');
        exit;
    }

    /**
     * 导出收入登记
     *
     * @param  Request  $request
     *
     * @throws \PhpOffice\PhpSpreadsheet\Exception
     * @throws \PhpOffice\PhpSpreadsheet\Writer\Exception
     */
    public function exportIncomeStati(Request $request)
    {
        $params = $request->input();
        $Letter = [
            'A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U',
        ];
        $data = $this->getIncomeList($params)->groupBy('线路')->groupBy('车牌号')->orderBy('线路')->get()->toArray();
        $line = $this->getIncomeList($params)->groupBy('线路')->orderBy('线路')->get()->toArray();
        foreach ($line as $k => $v) {
            $test = $v;
            if ($v['线路'] === 0) {
                unset($line[$k]);
                $test['线路'] = '停运';
                $line[] = $test;
            }
        }

        if (isset($params['start_date_time'])) {
            $start_date_time = date('Y-m-d', strtotime($params['start_date_time']));
        } else {
            $start_date_time = date('Y-m-d');
        }
        if (isset($params['end_date_time'])) {
            $end_date_time = date('Y-m-d', strtotime($params['end_date_time']));
        } else {
            $end_date_time = date('Y-m-d');
        }

        $data = $this->incomeTransData($data, $start_date_time, $end_date_time);
        // 创建一个Spreadsheet对象
        $spreadsheet = new Spreadsheet();
        // 设置文档属性
        $spreadsheet->getProperties()->setCreator('Maarten Balliauw')
            ->setLastModifiedBy('Maarten Balliauw')
            ->setTitle('Office 2007 XLSX Test Document')
            ->setSubject('Office 2007 XLSX Test Document')
            ->setDescription('Test document for Office 2007 XLSX, generated using PHP classes.')
            ->setKeywords('office 2007 openxml php')
            ->setCategory('Test result file');
        // 添加表头
        $spreadsheet->setActiveSheetIndex(0)
            ->setCellValue('A1', '日期')->setCellValue('B1', '线路')->setCellValue('C1', '车牌号')
            ->setCellValue('D1', '一卡通收入')->setCellValue('E1', '邮储收入')->setCellValue('F1', '现金收入')
            ->setCellValue('G1', '包车收入')->setCellValue('H1', '收入线路汇总');

        //居中
        $numberStyleCenter = [
            'alignment' => [
                'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER,
                'vertical'   => \PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER,
            ],
        ];

        for ($i = 0; $i < 8; $i++) {
            $spreadsheet->getActiveSheet()->getColumnDimension('H')->setWidth(20);
            $spreadsheet->getActiveSheet()->getStyle($Letter[$i].'1')
                ->applyFromArray($numberStyleCenter)->getAlignment()->setWrapText(true);
        }
        $spreadsheet->getActiveSheet()->getColumnDimension('A')->setWidth(30);
        $spreadsheet->getActiveSheet()->getColumnDimension('D')->setWidth(12);
        $spreadsheet->getActiveSheet()->getColumnDimension('E')->setWidth(12);
        $spreadsheet->getActiveSheet()->getColumnDimension('F')->setWidth(12);
        $spreadsheet->getActiveSheet()->getColumnDimension('G')->setWidth(12);
        $num = 2;
        $line_i = 0;
        $total_IPass = 0;
        $total_post = 0;
        $total_cash = 0;
        $total_chartered_bus = 0;
        $total_income = 0;
        foreach ($line as $k => $v) {
            $v['线路'] = $v['线路'] === 0 ? '停运' : $v['线路'];
            foreach ($data as $dk => $dv) {
                if ($v['线路'] === $dv['线路']) {
                    $spreadsheet->getActiveSheet()
                        ->setCellValue('A'.($num + $k + $line_i), $dv['date']);
                    if ($dv['线路'] !== '停运') {
                        $spreadsheet->getActiveSheet()->setCellValue('B'.($num + $k + $line_i), $dv['线路'].'路');
                    } else {
                        $spreadsheet->getActiveSheet()->setCellValue('B'.($num + $k + $line_i), $dv['线路']);
                    }
                    $spreadsheet->getActiveSheet()->setCellValue('C'.($num + $k + $line_i), $dv['车牌号']);
                    $spreadsheet->getActiveSheet()->setCellValue('D'.($num + $k + $line_i), $dv['total_IPass']);
                    $spreadsheet->getActiveSheet()->setCellValue('E'.($num + $k + $line_i), $dv['total_post']);
                    $spreadsheet->getActiveSheet()->setCellValue('F'.($num + $k + $line_i), $dv['total_cash']);
                    $spreadsheet->getActiveSheet()
                        ->setCellValue('G'.($num + $k + $line_i), round($dv['total_chartered_bus'], 2));
                    $spreadsheet->getActiveSheet()
                        ->setCellValue(
                            'H'.($num + $k + $line_i),
                            round(
                                (float) $dv['total_IPass'] + (float) $dv['total_post'] +
                                (float) $dv['total_cash'] + (float) $dv['total_chartered_bus'],
                                2
                            )
                        );
                    for ($i = 0; $i < 8; $i++) {
                        $spreadsheet->getActiveSheet()->getStyle($Letter[$i].($num + $k + $line_i))
                            ->getFont()->setSize(10);
                        $spreadsheet->getActiveSheet()->getStyle($Letter[$i].($num + $k + $line_i))
                            ->applyFromArray($numberStyleCenter)->getAlignment()->setWrapText(true);
                    }
                    $num++;
                }
            }
            $line_i++;
            for ($i = 0; $i < 8; $i++) {
                $spreadsheet->getActiveSheet()->getStyle($Letter[$i].($num + $k + $line_i - 1))->getFont()->setSize(10);
                $spreadsheet->getActiveSheet()->getStyle($Letter[$i].($num + $k + $line_i - 1))
                    ->applyFromArray($numberStyleCenter)->getAlignment()->setWrapText(true);
            }
            $spreadsheet->getActiveSheet()
                ->setCellValue('A'.($num + $k + $line_i - 1), $start_date_time.'-'.$end_date_time);
            $spreadsheet->getActiveSheet()->setCellValue('B'.($num + $k + $line_i - 1), '线路合计');
            $spreadsheet->getActiveSheet()->setCellValue('C'.($num + $k + $line_i - 1), '');
            $spreadsheet->getActiveSheet()->setCellValue('D'.($num + $k + $line_i - 1), $v['total_IPass']);
            $spreadsheet->getActiveSheet()->setCellValue('E'.($num + $k + $line_i - 1), $v['total_post']);
            $spreadsheet->getActiveSheet()->setCellValue('F'.($num + $k + $line_i - 1), $v['total_cash']);
            $spreadsheet->getActiveSheet()
                ->setCellValue('G'.($num + $k + $line_i - 1), round($v['total_chartered_bus'], 2));
            $spreadsheet->getActiveSheet()
                ->setCellValue(
                    'H'.($num + $k + $line_i - 1),
                    round(
                        (float) $v['total_IPass'] + (float) $v['total_post'] +
                        (float) $v['total_cash'] + (float) $v['total_chartered_bus'],
                        2
                    )
                );
            $total_IPass += $v['total_IPass'];
            $total_post += $v['total_post'];
            $total_cash += $v['total_cash'];
            $total_chartered_bus += $v['total_chartered_bus'];
            $total_income = (float) $total_IPass + (float) $total_post +
                (float) $total_cash + (float) $total_chartered_bus;
        }
        for ($i = 0; $i < 8; $i++) {
            $spreadsheet->getActiveSheet()
                ->getStyle($Letter[$i].($num + count($line) + $line_i))->getFont()->setSize(10);
            $spreadsheet->getActiveSheet()->getStyle($Letter[$i].($num + count($line) + $line_i))
                ->applyFromArray($numberStyleCenter)->getAlignment()->setWrapText(true);
        }
        $spreadsheet->getActiveSheet()
            ->setCellValue('A'.($num + count($line) + $line_i), $start_date_time.'-'.$end_date_time);
        $spreadsheet->getActiveSheet()->setCellValue('B'.($num + count($line) + $line_i), '总合计');
        $spreadsheet->getActiveSheet()->setCellValue('C'.($num + count($line) + $line_i), '');
        $spreadsheet->getActiveSheet()->setCellValue('D'.($num + count($line) + $line_i), $total_IPass);
        $spreadsheet->getActiveSheet()->setCellValue('E'.($num + count($line) + $line_i), $total_post);
        $spreadsheet->getActiveSheet()->setCellValue('F'.($num + count($line) + $line_i), $total_cash);
        $spreadsheet->getActiveSheet()->setCellValue('G'.($num + count($line) + $line_i), $total_chartered_bus);
        $spreadsheet->getActiveSheet()->setCellValue('H'.($num + count($line) + $line_i), round($total_income, 2));

        $spreadsheet->setActiveSheetIndex(0);
        // 重命名 worksheet
        $spreadsheet->getActiveSheet()->setTitle($start_date_time.'-'.$end_date_time);
        // 将活动工作表索引设置为第一个工作表，以便Excel将其作为第一个工作表打开
        // 将输出重定向到客户端的Web浏览器 (Xlsx)
        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        header('Content-Disposition: attachment;filename="'.$start_date_time.'-'.$end_date_time.'收入.xlsx"');
        header('Cache-Control: max-age=0');
        // 如果正在使用IE 9
        header('Cache-Control: max-age=1');
        // If you're serving to IE over SSL, then the following may be needed
        header('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); // Date in the past
        header('Last-Modified: '.gmdate('D, d M Y H:i:s').' GMT'); // always modified
        header('Cache-Control: cache, must-revalidate'); // HTTP/1.1
        header('Pragma: public'); // HTTP/1.0
        $writer = IOFactory::createWriter($spreadsheet, 'Xlsx');
        $writer->save('php://output');
        exit;
    }

    /**
     * 导出里程登记表
     *
     * @param  Request  $request
     *
     * @throws \PhpOffice\PhpSpreadsheet\Exception
     * @throws \PhpOffice\PhpSpreadsheet\Writer\Exception
     */
    public function exportKmStati(Request $request)
    {
        $params = $request->input();
        $Letter = [
            'A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U',
        ];
        $data = $this->getKmList($params)
            ->groupBy('license_plate_num')
            ->groupBy('line')
            ->orderBy('line')
            ->orderBy('license_plate_num')
            ->get()
            ->toArray();
        $line = $this->getKmList($params)->groupBy('line')->get()->toArray();
        foreach ($line as &$v) {
            if ($v['line'] === 0) {
                $v['line'] = 99;
            }
        }
        $line = collect($line)->sortBy('line')->toArray();
        $line = $this->rebuildKeys($line);
        if (isset($params['start_date_time'])) {
            $start_date_time = date('Y-m-d', strtotime($params['start_date_time']));
        } else {
            $start_date_time = date('Y-m-d');
        }
        if (isset($params['end_date_time'])) {
            $end_date_time = date('Y-m-d', strtotime($params['end_date_time']));
        } else {
            $end_date_time = date('Y-m-d');
        }
        // 创建一个Spreadsheet对象
        $spreadsheet = new Spreadsheet();
        // 设置文档属性
        $spreadsheet->getProperties()->setCreator('Maarten Balliauw')
            ->setLastModifiedBy('Maarten Balliauw')
            ->setTitle('Office 2007 XLSX Test Document')
            ->setSubject('Office 2007 XLSX Test Document')
            ->setDescription('Test document for Office 2007 XLSX, generated using PHP classes.')
            ->setKeywords('office 2007 openxml php')
            ->setCategory('Test result file');
        // 添加表头
        $spreadsheet->setActiveSheetIndex(0)
            ->setCellValue('A1', '日期')->setCellValue('B1', '线路')->setCellValue('C1', '车牌号')
            ->setCellValue('D1', '总里程');

        //居中
        $numberStyleCenter = [
            'alignment' => [
                'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER,
                'vertical'   => \PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER,
            ],
        ];
        //右
        $numberStyleRight = [
            'alignment' => [
                'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_RIGHT,
                'vertical'   => \PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER,
            ],
        ];

        for ($i = 0; $i < 4; $i++) {
            $spreadsheet->getActiveSheet()->getColumnDimension('D')->setWidth(12);
            $spreadsheet->getActiveSheet()->getStyle($Letter[$i].'1')
                ->applyFromArray($numberStyleCenter)->getAlignment()->setWrapText(true);
        }
        $spreadsheet->getActiveSheet()->getColumnDimension('A')->setWidth(30);
        $num = 2;
        $line_i = 0;
        $total_km = 0;
        foreach ($line as $k => $v) {
            foreach ($data as $dk => $dv) {
                $dv['line'] = $dv['line'] !== 0 ? $dv['line'] : 99;
                if ($v['line'] === $dv['line']) {
                    $spreadsheet->getActiveSheet()
                        ->setCellValue('A'.($num + $k + $line_i), $start_date_time.'-'.$end_date_time);
                    $linNum = $dv['line'] === 99 ? '停运' : $dv['line'].'路';
                    $spreadsheet->getActiveSheet()->setCellValue('B'.($num + $k + $line_i), $linNum);
                    $spreadsheet->getActiveSheet()->setCellValue('C'.($num + $k + $line_i), $dv['license_plate_num']);
                    $spreadsheet->getActiveSheet()->setCellValue('D'.($num + $k + $line_i), $dv['total_km']);
                    for ($i = 0; $i < 4; $i++) {
                        $spreadsheet->getActiveSheet()
                            ->getStyle($Letter[$i].($num + $k + $line_i))->getFont()->setSize(10);
                        $spreadsheet->getActiveSheet()
                            ->getStyle($Letter[$i].($num + $k + $line_i))
                            ->applyFromArray($numberStyleCenter)->getAlignment()->setWrapText(true);
                    }
                    $num++;
                }
            }
            $line_i++;
            for ($i = 0; $i < 4; $i++) {
                $spreadsheet->getActiveSheet()->getStyle($Letter[$i].($num + $k + $line_i - 1))->getFont()->setSize(10);
                $spreadsheet->getActiveSheet()->getStyle($Letter[$i].($num + $k + $line_i - 1))
                    ->applyFromArray($numberStyleCenter)->getAlignment()->setWrapText(true);
            }
            $spreadsheet->getActiveSheet()
                ->setCellValue('A'.($num + $k + $line_i - 1), $start_date_time.'-'.$end_date_time);
            $spreadsheet->getActiveSheet()->setCellValue('B'.($num + $k + $line_i - 1), '线路合计');
            $spreadsheet->getActiveSheet()->setCellValue('C'.($num + $k + $line_i - 1), '');
            $spreadsheet->getActiveSheet()->setCellValue('D'.($num + $k + $line_i - 1), $v['total_km']);
            $total_km += $v['total_km'];
        }
        for ($i = 0; $i < 4; $i++) {
            $spreadsheet->getActiveSheet()
                ->getStyle($Letter[$i].($num + count($line) + $line_i))->getFont()->setSize(10);
            $spreadsheet->getActiveSheet()->getStyle($Letter[$i].($num + count($line) + $line_i))
                ->applyFromArray($numberStyleCenter)->getAlignment()->setWrapText(true);
        }
        $spreadsheet->getActiveSheet()->getStyle('D2:D'.($num + count($line) + $line_i))
            ->applyFromArray($numberStyleRight);
        $spreadsheet->getActiveSheet()->getStyle('D2:D'.($num + count($line) + $line_i))
            ->getNumberFormat()->setFormatCode('#,##0.00');
        $spreadsheet->getActiveSheet()
            ->setCellValue('A'.($num + count($line) + $line_i), $start_date_time.'-'.$end_date_time);
        $spreadsheet->getActiveSheet()->setCellValue('B'.($num + count($line) + $line_i), '总合计');
        $spreadsheet->getActiveSheet()->setCellValue('C'.($num + count($line) + $line_i), '');
        $spreadsheet->getActiveSheet()->setCellValue('D'.($num + count($line) + $line_i), $total_km);

        $spreadsheet->setActiveSheetIndex(0);
        // 重命名 worksheet
        $spreadsheet->getActiveSheet()->setTitle($start_date_time.'-'.$end_date_time);
        // 将活动工作表索引设置为第一个工作表，以便Excel将其作为第一个工作表打开
        // 将输出重定向到客户端的Web浏览器 (Xlsx)
        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        header('Content-Disposition: attachment;filename="'.$start_date_time.'-'.$end_date_time.'里程.xlsx"');
        header('Cache-Control: max-age=0');
        // 如果正在使用IE 9
        header('Cache-Control: max-age=1');
        // If you're serving to IE over SSL, then the following may be needed
        header('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); // Date in the past
        header('Last-Modified: '.gmdate('D, d M Y H:i:s').' GMT'); // always modified
        header('Cache-Control: cache, must-revalidate'); // HTTP/1.1
        header('Pragma: public'); // HTTP/1.0
        $writer = IOFactory::createWriter($spreadsheet, 'Xlsx');
        $writer->save('php://output');
        exit;
    }

    /**
     * 导出逸华气耗登记表
     *
     * @param  Request  $request
     *
     * @throws \PhpOffice\PhpSpreadsheet\Exception
     * @throws \PhpOffice\PhpSpreadsheet\Writer\Exception
     */
    public function exportYhGasStati(Request $request)
    {
        $params = $request->input();
        $Letter = [
            'A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U',
        ];
        $yh_data = $this->getYhList($params)->groupBy('线路')->groupBy('车牌号')->orderBy('线路')->get()->toArray();
        $yh_line = $this->getYhList($params)->groupBy('线路')->orderBy('线路')->get()->toArray();
        if (isset($params['start_date_time'])) {
            $start_date_time = date('Y-m-d', strtotime($params['start_date_time']));
        } else {
            $start_date_time = date('Y-m-d');
        }
        if (isset($params['end_date_time'])) {
            $end_date_time = date('Y-m-d', strtotime($params['end_date_time']));
        } else {
            $end_date_time = date('Y-m-d');
        }
        // 创建一个Spreadsheet对象
        $spreadsheet = new Spreadsheet();
        // 设置文档属性
        $spreadsheet->getProperties()->setCreator('Maarten Balliauw')
            ->setLastModifiedBy('Maarten Balliauw')
            ->setTitle('Office 2007 XLSX Test Document')
            ->setSubject('Office 2007 XLSX Test Document')
            ->setDescription('Test document for Office 2007 XLSX, generated using PHP classes.')
            ->setKeywords('office 2007 openxml php')
            ->setCategory('Test result file');
        // 添加表头
        $spreadsheet->setActiveSheetIndex(0)
            ->setCellValue('A1', '日期')->setCellValue('B1', '线路')->setCellValue('C1', '车牌号')
            ->setCellValue('D1', '金额')->setCellValue('E1', '气量');

        //居中
        $numberStyleCenter = [
            'alignment' => [
                'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER,
                'vertical'   => \PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER,
            ],
        ];

        for ($i = 0; $i < 5; $i++) {
            $spreadsheet->getActiveSheet()->getStyle($Letter[$i].'1')->applyFromArray($numberStyleCenter)
                ->getAlignment()->setWrapText(true);
        }
        $spreadsheet->getActiveSheet()->getColumnDimension('A')->setWidth(30);
        $num = 2;
        $yh_line_i = 0;
        $total_amt = 0;
        $total_volume = 0;
        foreach ($yh_line as $k => $v) {
            foreach ($yh_data as $dk => $dv) {
                if ($v['线路'] == $dv['线路']) {
                    $spreadsheet->getActiveSheet()
                        ->setCellValue('A'.($num + $k + $yh_line_i), $start_date_time.'-'.$end_date_time);
                    $spreadsheet->getActiveSheet()->setCellValue('B'.($num + $k + $yh_line_i), $dv['线路'].'路');
                    $spreadsheet->getActiveSheet()->setCellValue('C'.($num + $k + $yh_line_i), $dv['车牌号']);
                    $spreadsheet->getActiveSheet()->setCellValue('D'.($num + $k + $yh_line_i), $dv['total_amount']);
                    $spreadsheet->getActiveSheet()->setCellValue('E'.($num + $k + $yh_line_i), $dv['total_volume']);
                    for ($i = 0; $i < 5; $i++) {
                        $spreadsheet->getActiveSheet()->getStyle($Letter[$i].($num + $k + $yh_line_i))
                            ->getFont()->setSize(10);
                        $spreadsheet->getActiveSheet()->getStyle($Letter[$i].($num + $k + $yh_line_i))
                            ->applyFromArray($numberStyleCenter)->getAlignment()->setWrapText(true);
                    }
                    $num++;
                }
            }
            $yh_line_i++;
            for ($i = 0; $i < 5; $i++) {
                $spreadsheet->getActiveSheet()->getStyle($Letter[$i].($num + $k + $yh_line_i - 1))
                    ->getFont()->setSize(10);
                $spreadsheet->getActiveSheet()->getStyle($Letter[$i].($num + $k + $yh_line_i - 1))
                    ->applyFromArray($numberStyleCenter)->getAlignment()->setWrapText(true);
            }
            $spreadsheet->getActiveSheet()
                ->setCellValue('A'.($num + $k + $yh_line_i - 1), $start_date_time.'-'.$end_date_time);
            $spreadsheet->getActiveSheet()->setCellValue('B'.($num + $k + $yh_line_i - 1), '线路合计');
            $spreadsheet->getActiveSheet()->setCellValue('C'.($num + $k + $yh_line_i - 1), '');
            $spreadsheet->getActiveSheet()->setCellValue('D'.($num + $k + $yh_line_i - 1), $v['total_amount']);
            $spreadsheet->getActiveSheet()->setCellValue('E'.($num + $k + $yh_line_i - 1), $v['total_volume']);
            $total_amt += $v['total_amount'];
            $total_volume += $v['total_volume'];
        }
        for ($i = 0; $i < 5; $i++) {
            $spreadsheet->getActiveSheet()->getStyle($Letter[$i].($num + count($yh_line) + $yh_line_i))
                ->getFont()->setSize(10);
            $spreadsheet->getActiveSheet()->getStyle($Letter[$i].($num + count($yh_line) + $yh_line_i))
                ->applyFromArray($numberStyleCenter)->getAlignment()->setWrapText(true);
        }
        $spreadsheet->getActiveSheet()
            ->setCellValue('A'.($num + count($yh_line) + $yh_line_i), $start_date_time.'-'.$end_date_time);
        $spreadsheet->getActiveSheet()->setCellValue('B'.($num + count($yh_line) + $yh_line_i), '总合计');
        $spreadsheet->getActiveSheet()->setCellValue('C'.($num + count($yh_line) + $yh_line_i), '');
        $spreadsheet->getActiveSheet()->setCellValue('D'.($num + count($yh_line) + $yh_line_i), $total_amt);
        $spreadsheet->getActiveSheet()->setCellValue('E'.($num + count($yh_line) + $yh_line_i), $total_volume);

        $spreadsheet->setActiveSheetIndex(0);
        // 重命名 worksheet
        $spreadsheet->getActiveSheet()->setTitle('逸华加气');
        // 将活动工作表索引设置为第一个工作表，以便Excel将其作为第一个工作表打开
        // 将输出重定向到客户端的Web浏览器 (Xlsx)
        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        header('Content-Disposition: attachment;filename="'.$start_date_time.'-'.$end_date_time.'逸华气耗.xlsx"');
        header('Cache-Control: max-age=0');
        // 如果正在使用IE 9
        header('Cache-Control: max-age=1');
        // If you're serving to IE over SSL, then the following may be needed
        header('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); // Date in the past
        header('Last-Modified: '.gmdate('D, d M Y H:i:s').' GMT'); // always modified
        header('Cache-Control: cache, must-revalidate'); // HTTP/1.1
        header('Pragma: public'); // HTTP/1.0
        $writer = IOFactory::createWriter($spreadsheet, 'Xlsx');
        $writer->save('php://output');
        exit;
    }

    /**
     * 导出气耗登记表
     *
     * @param  Request  $request
     *
     * @throws \PhpOffice\PhpSpreadsheet\Exception
     * @throws \PhpOffice\PhpSpreadsheet\Writer\Exception
     */
    public function exportSkGasStati(Request $request)
    {
        $params = $request->input();
        $Letter = [
            'A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U',
        ];
        $sk_data = $this->getSkList($params)->groupBy('线路')->groupBy('车牌号')->orderBy('线路')->get()->toArray();
        $sk_line = $this->getSkList($params)->groupBy('线路')->orderBy('线路')->get()->toArray();
        if (isset($params['start_date_time'])) {
            $start_date_time = date('Y-m-d', strtotime($params['start_date_time']));
        } else {
            $start_date_time = date('Y-m-d');
        }
        if (isset($params['end_date_time'])) {
            $end_date_time = date('Y-m-d', strtotime($params['end_date_time']));
        } else {
            $end_date_time = date('Y-m-d');
        }
        // 创建一个Spreadsheet对象
        $spreadsheet = new Spreadsheet();
        // 设置文档属性
        $spreadsheet->getProperties()->setCreator('Maarten Balliauw')
            ->setLastModifiedBy('Maarten Balliauw')
            ->setTitle('Office 2007 XLSX Test Document')
            ->setSubject('Office 2007 XLSX Test Document')
            ->setDescription('Test document for Office 2007 XLSX, generated using PHP classes.')
            ->setKeywords('office 2007 openxml php')
            ->setCategory('Test result file');

        $spreadsheet->setActiveSheetIndex(0)
            ->setCellValue('A1', '日期')->setCellValue('B1', '线路')->setCellValue('C1', '车牌号')
            ->setCellValue('D1', '金额')->setCellValue('E1', '气量');

        //居中
        $numberStyleCenter = [
            'alignment' => [
                'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER,
                'vertical'   => \PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER,
            ],
        ];

        for ($i = 0; $i < 5; $i++) {
            $spreadsheet->getActiveSheet()->getStyle($Letter[$i].'1')->applyFromArray($numberStyleCenter)
                ->getAlignment()->setWrapText(true);
        }
        $spreadsheet->getActiveSheet()->getColumnDimension('A')->setWidth(30);
        $num = 2;
        $sk_line_i = 0;
        $sk_total_amt = 0;
        $sk_total_volume = 0;
        foreach ($sk_line as $k => $v) {
            foreach ($sk_data as $dk => $dv) {
                if ($v['线路'] === $dv['线路']) {
                    $spreadsheet->getActiveSheet()->setCellValue(
                        'A'.($num + $k + $sk_line_i),
                        $start_date_time.'-'.$end_date_time
                    );
                    $spreadsheet->getActiveSheet()->setCellValue('B'.($num + $k + $sk_line_i), (int) $dv['线路'] === 0 ? '停运' : $dv['线路'].'路');
                    $spreadsheet->getActiveSheet()->setCellValue('C'.($num + $k + $sk_line_i), $dv['车牌号']);
                    $spreadsheet->getActiveSheet()->setCellValue('D'.($num + $k + $sk_line_i), $dv['total_amount']);
                    $spreadsheet->getActiveSheet()->setCellValue('E'.($num + $k + $sk_line_i), $dv['total_volume']);
                    for ($i = 0; $i < 5; $i++) {
                        $spreadsheet->getActiveSheet()->getStyle($Letter[$i].($num + $k + $sk_line_i))
                            ->getFont()->setSize(10);
                        $spreadsheet->getActiveSheet()->getStyle($Letter[$i].($num + $k + $sk_line_i))
                            ->applyFromArray($numberStyleCenter)->getAlignment()->setWrapText(true);
                    }
                    $num++;
                }
            }
            $sk_line_i++;
            for ($i = 0; $i < 5; $i++) {
                $spreadsheet->getActiveSheet()->getStyle($Letter[$i].($num + $k + $sk_line_i - 1))
                    ->getFont()->setSize(10);
                $spreadsheet->getActiveSheet()->getStyle($Letter[$i].($num + $k + $sk_line_i - 1))
                    ->applyFromArray($numberStyleCenter)->getAlignment()->setWrapText(true);
            }
            $spreadsheet->getActiveSheet()->setCellValue(
                'A'.($num + $k + $sk_line_i - 1),
                $start_date_time.'-'.$end_date_time
            );
            $spreadsheet->getActiveSheet()->setCellValue('B'.($num + $k + $sk_line_i - 1), '线路合计');
            $spreadsheet->getActiveSheet()->setCellValue('C'.($num + $k + $sk_line_i - 1), '');
            $spreadsheet->getActiveSheet()->setCellValue('D'.($num + $k + $sk_line_i - 1), $v['total_amount']);
            $spreadsheet->getActiveSheet()->setCellValue('E'.($num + $k + $sk_line_i - 1), $v['total_volume']);
            $sk_total_amt += $v['total_amount'];
            $sk_total_volume += $v['total_volume'];
        }
        for ($i = 0; $i < 5; $i++) {
            $spreadsheet->getActiveSheet()->getStyle($Letter[$i].($num + count($sk_line) + $sk_line_i))
                ->getFont()->setSize(10);
            $spreadsheet->getActiveSheet()->getStyle($Letter[$i].($num + count($sk_line) + $sk_line_i))
                ->applyFromArray($numberStyleCenter)->getAlignment()->setWrapText(true);
        }
        $spreadsheet->getActiveSheet()
            ->setCellValue('A'.($num + count($sk_line) + $sk_line_i), $start_date_time.'-'.$end_date_time);
        $spreadsheet->getActiveSheet()->setCellValue('B'.($num + count($sk_line) + $sk_line_i), '总合计');
        $spreadsheet->getActiveSheet()->setCellValue('C'.($num + count($sk_line) + $sk_line_i), '');
        $spreadsheet->getActiveSheet()->setCellValue('D'.($num + count($sk_line) + $sk_line_i), $sk_total_amt);
        $spreadsheet->getActiveSheet()->setCellValue('E'.($num + count($sk_line) + $sk_line_i), $sk_total_volume);

        $spreadsheet->setActiveSheetIndex(0);
        // 重命名 worksheet
        $spreadsheet->getActiveSheet()->setTitle('顺康加气');
        // 将活动工作表索引设置为第一个工作表，以便Excel将其作为第一个工作表打开
        // 将输出重定向到客户端的Web浏览器 (Xlsx)
        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        header('Content-Disposition: attachment;filename="'.$start_date_time.'-'.$end_date_time.'顺康气耗.xlsx"');
        header('Cache-Control: max-age=0');
        // 如果正在使用IE 9
        header('Cache-Control: max-age=1');
        // If you're serving to IE over SSL, then the following may be needed
        header('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); // Date in the past
        header('Last-Modified: '.gmdate('D, d M Y H:i:s').' GMT'); // always modified
        header('Cache-Control: cache, must-revalidate'); // HTTP/1.1
        header('Pragma: public'); // HTTP/1.0
        $writer = IOFactory::createWriter($spreadsheet, 'Xlsx');
        $writer->save('php://output');
        exit;
    }

    /**
     * 导出气耗登记表
     *
     * @param  Request  $request
     *
     * @throws \PhpOffice\PhpSpreadsheet\Exception
     * @throws \PhpOffice\PhpSpreadsheet\Writer\Exception
     */
    public function exportYhAmtGasStati(Request $request)
    {
        $params = $request->input();
        $Letter = [
            'A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U',
        ];
        $yh_amt_data = $this->getYhAmtList($params)
            ->groupBy('line')
            ->groupBy('license_plate_num')
            ->orderBy('line')
            ->get()
            ->toArray();
        $yh_amt_line = $this->getYhAmtList($params)->groupBy('line')->orderBy('line')->get()->toArray();
        if (isset($params['start_date_time'])) {
            $start_date_time = date('Y-m-d', strtotime($params['start_date_time']));
        } else {
            $start_date_time = date('Y-m-d');
        }
        if (isset($params['end_date_time'])) {
            $end_date_time = date('Y-m-d', strtotime($params['end_date_time']));
        } else {
            $end_date_time = date('Y-m-d');
        }
        // 创建一个Spreadsheet对象
        $spreadsheet = new Spreadsheet();
        // 设置文档属性
        $spreadsheet->getProperties()->setCreator('Maarten Balliauw')
            ->setLastModifiedBy('Maarten Balliauw')
            ->setTitle('Office 2007 XLSX Test Document')
            ->setSubject('Office 2007 XLSX Test Document')
            ->setDescription('Test document for Office 2007 XLSX, generated using PHP classes.')
            ->setKeywords('office 2007 openxml php')
            ->setCategory('Test result file');
        // 添加表头
        $spreadsheet->setActiveSheetIndex(0)
            ->setCellValue('A1', '日期')->setCellValue('B1', '线路')->setCellValue('C1', '车牌号')
            ->setCellValue('D1', '金额合计')->setCellValue('E1', '气量合计');

        //居中
        $numberStyleCenter = [
            'alignment' => [
                'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER,
                'vertical'   => \PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER,
            ],
        ];

        for ($i = 0; $i < 5; $i++) {
            $spreadsheet->getActiveSheet()->getColumnDimension($Letter[$i])->setWidth(12);
            $spreadsheet->getActiveSheet()->getStyle($Letter[$i].'1')->applyFromArray($numberStyleCenter)
                ->getAlignment()->setWrapText(true);
        }
        $spreadsheet->getActiveSheet()->getColumnDimension('A')->setWidth(30);
        $num = 2;
        $yh_amt_line_i = 0;
        $yh_amt_total_amt = 0;
        $yh_amt_total_volume = 0;
        foreach ($yh_amt_line as $k => $v) {
            foreach ($yh_amt_data as $dk => $dv) {
                if ($v['line'] === $dv['line']) {
                    $spreadsheet->getActiveSheet()->setCellValue(
                        'A'.($num + $k + $yh_amt_line_i),
                        $start_date_time.'-'.$end_date_time
                    );
                    $spreadsheet->getActiveSheet()->setCellValue('B'.($num + $k + $yh_amt_line_i), $dv['line'].'路');
                    $spreadsheet->getActiveSheet()->setCellValue(
                        'C'.($num + $k + $yh_amt_line_i),
                        $dv['license_plate_num']
                    );
                    $spreadsheet->getActiveSheet()->setCellValue('D'.($num + $k + $yh_amt_line_i), $dv['total_amount']);
                    $spreadsheet->getActiveSheet()->setCellValue('E'.($num + $k + $yh_amt_line_i), $dv['total_volume']);
                    for ($i = 0; $i < 5; $i++) {
                        $spreadsheet->getActiveSheet()->getStyle($Letter[$i].($num + $k + $yh_amt_line_i))
                            ->getFont()->setSize(10);
                        $spreadsheet->getActiveSheet()->getStyle($Letter[$i].($num + $k + $yh_amt_line_i))
                            ->applyFromArray($numberStyleCenter)->getAlignment()->setWrapText(true);
                    }
                    $num++;
                }
            }
            $yh_amt_line_i++;
            for ($i = 0; $i < 5; $i++) {
                $spreadsheet->getActiveSheet()->getStyle($Letter[$i].($num + $k + $yh_amt_line_i - 1))
                    ->getFont()->setSize(10);
                $spreadsheet->getActiveSheet()->getStyle($Letter[$i].($num + $k + $yh_amt_line_i - 1))
                    ->applyFromArray($numberStyleCenter)->getAlignment()->setWrapText(true);
            }
            $spreadsheet->getActiveSheet()->setCellValue(
                'A'.($num + $k + $yh_amt_line_i - 1),
                $start_date_time.'-'.$end_date_time
            );
            $spreadsheet->getActiveSheet()->setCellValue('B'.($num + $k + $yh_amt_line_i - 1), '线路合计');
            $spreadsheet->getActiveSheet()->setCellValue('C'.($num + $k + $yh_amt_line_i - 1), '');
            $spreadsheet->getActiveSheet()->setCellValue('D'.($num + $k + $yh_amt_line_i - 1), $v['total_amount']);
            $spreadsheet->getActiveSheet()->setCellValue('E'.($num + $k + $yh_amt_line_i - 1), $v['total_volume']);
            $yh_amt_total_amt += $v['total_amount'];
            $yh_amt_total_volume += $v['total_volume'];
        }
        for ($i = 0; $i < 5; $i++) {
            $spreadsheet->getActiveSheet()->getStyle($Letter[$i].($num + count($yh_amt_line) + $yh_amt_line_i))
                ->getFont()->setSize(10);
            $spreadsheet->getActiveSheet()->getStyle($Letter[$i].($num + count($yh_amt_line) + $yh_amt_line_i))
                ->applyFromArray($numberStyleCenter)->getAlignment()->setWrapText(true);
        }
        $spreadsheet->getActiveSheet()
            ->setCellValue('A'.($num + count($yh_amt_line) + $yh_amt_line_i), $start_date_time.'-'.$end_date_time);
        $spreadsheet->getActiveSheet()->setCellValue('B'.($num + count($yh_amt_line) + $yh_amt_line_i), '总合计');
        $spreadsheet->getActiveSheet()->setCellValue('C'.($num + count($yh_amt_line) + $yh_amt_line_i), '');
        $spreadsheet->getActiveSheet()
            ->setCellValue('D'.($num + count($yh_amt_line) + $yh_amt_line_i), $yh_amt_total_amt);
        $spreadsheet->getActiveSheet()
            ->setCellValue('E'.($num + count($yh_amt_line) + $yh_amt_line_i), $yh_amt_total_volume);

        $spreadsheet->setActiveSheetIndex(0);
        // 重命名 worksheet
        $spreadsheet->getActiveSheet()->setTitle('逸华检气罐费用');
        // 将活动工作表索引设置为第一个工作表，以便Excel将其作为第一个工作表打开
        // 将输出重定向到客户端的Web浏览器 (Xlsx)
        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        header('Content-Disposition: attachment;filename="'.$start_date_time.'-'.$end_date_time.'逸华检气罐.xlsx"');
        header('Cache-Control: max-age=0');
        // 如果正在使用IE 9
        header('Cache-Control: max-age=1');
        // If you're serving to IE over SSL, then the following may be needed
        header('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); // Date in the past
        header('Last-Modified: '.gmdate('D, d M Y H:i:s').' GMT'); // always modified
        header('Cache-Control: cache, must-revalidate'); // HTTP/1.1
        header('Pragma: public'); // HTTP/1.0
        $writer = IOFactory::createWriter($spreadsheet, 'Xlsx');
        $writer->save('php://output');
        exit;
    }

    public function rebuildKeys($array)
    {
        if (! is_array($array)) {
            return $array;
        }
        $keys = implode('', array_keys($array));
        if (is_numeric($keys)) {
            $array = array_values($array);
        }
        $array = array_map([$this, 'rebuildKeys'], $array);

        return $array;
    }
}
